/****** Object:  StoredProcedure [dbo].[sp_GetNsToChuc]    Script Date: 08/25/2011 23:17:47 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetNsToChucByTCHC]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetNsToChucByTCHC]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetNsToChuc]    Script Date: 08/25/2011 23:17:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_GetNsToChucByTCHC]
	@ToChucHanhChinhID int,
    @Debug int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
 IF @ToChucHanhChinhID IS NULL 
 BEGIN
  RAISERROR ('Ban phai nhap NSToChucHanhChinhID',16,1)
  RETURN
 END
 
declare @tchcLevel int
declare @tchcTree table(tchcID int, tchcLevel int)
declare @tmptchcID int

exec sp_GetTCHCLevel @ToChucHanhChinhID, @tchcLevel out

declare @i as int
set @i = @tchcLevel + 1
set @tmptchcID = @ToChucHanhChinhID
INSERT @tchcTree VALUES (@ToChucHanhChinhID, @tchcLevel);

while @i <= 2
begin
	INSERT @tchcTree 
		SELECT tchc.ToChucHanhChinhID,tchc.ToChucHanhChinhLevel 
		FROM CHToChucHanhChinh tchc
		WHERE tchc.ToChucHanhChinhIDCha IN (SELECT DISTINCT tchcID FROM @tchcTree)
	
	set @i = @i + 1
end 

--SELECT * FROM @tchcTree
 

  SELECT 
   --ToChucID as int,
   --CONVERT(int, NULL) as ToChucID,
   --tc.TenToChucShort,
   --tc.TenToChucFull,
   --tc.ToChucIDCha,
   --tc.ToChucLevel,
   --tc.XepHangDonViID,
   tchc.ToChucHanhChinhID as int,
   CONVERT(int, NULL) as ToChucHanhChinhID,
   tchc.TenToChucHanhChinh,
   tchc.ToChucHanhChinhIDCha,
   tchc.ToChucHanhChinhLevel,
   tchc.LoaiToChucHanhChinh,
   cc.CodeValue as TenLoaiToChucHanhChinh,
   
   Convert(int,NULL) as Nu, --K
   Convert(int,NULL) as DangVien,--L
   Convert(int,NULL) as DanTocItNguoi,--M
   Convert(int,NULL) as Duoi30Tuoi,--N
   Convert(int,NULL) as Tuoi30_50,--O
   Convert(int,NULL) as Tuoi51_60,--P
   Convert(int,NULL) as Nu_54Tuoi,--Q
   Convert(int,NULL) as Nam_59Tuoi,--R

   Convert(int,NULL) as LLCT_CaoCap,--T
   Convert(int,NULL) as LLCT_TrungCap,--U
   Convert(int,NULL) as LLCT_SoCap,--V
   Convert(int,NULL) as AnhVan_CuNhan,--W
   Convert(int,NULL) as AnhVan_CoSo,--X
   Convert(int,NULL) as NNKhac_CuNhan,--Y
   Convert(int,NULL) as NNKhac_CoSo,--Z
   Convert(int,NULL) as TinHoc_CuNhan,--AA
   Convert(int,NULL) as TinHoc_CoSo,--AB

   Convert(int,NULL) as ThayThuocNhanDan,--AC
   Convert(int,NULL) as ThayThuocUuTu,--AD
   Convert(int,NULL) as NhaGiaoNhanDan,--AE
   Convert(int,NULL) as NhaGiaoUuTu,--AF

   Convert(int,NULL) as TongSoBacSi,--AS
   Convert(int,NULL) as TongSoBacSi_Nu,--AT
   Convert(int,NULL) as GiaoSuY,--AU
   Convert(int,NULL) as GiaoSuY_Nu,--AV
   Convert(int,NULL) as PhoGiaoSuY,--AW
   Convert(int,NULL) as PhoGiaoSuY_Nu,--AX
   Convert(int,NULL) as TienSiY,--AY
   Convert(int,NULL) as TienSiY_Nu,--AZ
   Convert(int,NULL) as ThacSiY,--BA
   Convert(int,NULL) as ThacSiY_Nu,--BB
   Convert(int,NULL) as BacSiCK2,--BC
   Convert(int,NULL) as BacSiCK2_Nu,--BD
   Convert(int,NULL) as BacSiCK1,--BE
   Convert(int,NULL) as BacSiCK1_Nu,--BF
   Convert(int,NULL) as BacSiNha,--BG
   Convert(int,NULL) as BacSiNha_Nu,--BH
   Convert(int,NULL) as NgachBacSi_ChuyenVienCaoCap,--BK
   Convert(int,NULL) as NgachBacSi_ChuyenVienChinh,--BL
   Convert(int,NULL) as NgachBacSi_ChuyenVien,--BM
   Convert(int,NULL) as NgachBacSiCaoCap,--BN
   Convert(int,NULL) as NgachBacSiChinh,--BO
   Convert(int,NULL) as NgachBacSi,--BP
   
   Convert(int,NULL) as DuocSiDaiHoc,--BR
   Convert(int,NULL) as DuocSiDaiHoc_Nu,--BS
   Convert(int,NULL) as GiaoSuDuoc,--BT
   Convert(int,NULL) as GiaoSuDuoc_Nu,--BU
   Convert(int,NULL) as PhoGiaoSuDuoc,--BV
   Convert(int,NULL) as PhoGiaoSuDuoc_Nu,--BW
   Convert(int,NULL) as TienSiDuoc,--BX
   Convert(int,NULL) as TienSiDuoc_Nu,--BY
   Convert(int,NULL) as ThacSiDuoc,--BZ
   Convert(int,NULL) as ThacSiDuoc_Nu,--CA
   Convert(int,NULL) as DuocSiCK2,--CB
   Convert(int,NULL) as DuocSiCK2_Nu,--CC
   Convert(int,NULL) as DuocSiCK1,--CD
   Convert(int,NULL) as DuocSiCK1_Nu,--CE
   Convert(int,NULL) as NgachDuocSi_ChuyenVienCaoCap,--CH
   Convert(int,NULL) as NgachDuocSi_ChuyenVienChinh,--CI
   Convert(int,NULL) as NgachDuocSi_ChuyenVien,--CJ
   Convert(int,NULL) as NgachDuocSiCaoCap,--CK
   Convert(int,NULL) as NgachDuocSiChinh,--CL
   Convert(int,NULL) as NgachDuocSi,--CM
   
   Convert(int,NULL) as ThacSiYTeCongCong,--CO
   Convert(int,NULL) as ThacSiYTeCongCong_Nu,--CP
   Convert(int,NULL) as DaiHocYTeCongCong,--CQ
   Convert(int,NULL) as DaiHocYTeCongCong_Nu,--CR
   Convert(int,NULL) as NgachYTeCongCongChinh,--CS
   Convert(int,NULL) as NgachYTeCongCong,--CT
   
   Convert(int,NULL) as YSi,--CU
   Convert(int,NULL) as YSi_Nu,--CV
   Convert(int,NULL) as NgachYSi,--CW
   Convert(int,NULL) as NgachYSiCanSu,--CX
   
   Convert(int,NULL) as KTVNhaKhoa,--CZ
   Convert(int,NULL) as KTVNhaKhoa_Nu,--DA
   Convert(int,NULL) as NgachKTVNhaKhoa,--DB
   
   Convert(int,NULL) as KTVY,--DC
   Convert(int,NULL) as KTVY_Nu,--DD
   Convert(int,NULL) as KTVYDaiHoc,--DE
   Convert(int,NULL) as KTVYCaoDang,--DF
   Convert(int,NULL) as KTVYTrungHoc,--DG
   Convert(int,NULL) as KTVYSoHoc,--DH
   Convert(int,NULL) as NgachKTVYChinh,--DI
   Convert(int,NULL) as NgachKTVY,--DJ
   Convert(int,NULL) as NgachKTVYCaoDang,--DK
   Convert(int,NULL) as NgachKTVYTrungHoc,--DL
   Convert(int,NULL) as NgachKTVYSoHoc,--DL
   
   Convert(int,NULL) as DuocSiTH_KTVTrungHocDuoc,--DM
   Convert(int,NULL) as DuocSiTH_KTVTrungHocDuoc_Nu,--DN
   Convert(int,NULL) as NgachDuocSiTH,--DO
   
   Convert(int,NULL) as DieuDuong,--DQ
   Convert(int,NULL) as DieuDuong_Nu,--DR
   Convert(int,NULL) as DieuDuongDaiHoc,--DS
   Convert(int,NULL) as DieuDuongCaoDang,--DT
   Convert(int,NULL) as DieuDuongTrungHoc,--DU
   Convert(int,NULL) as DieuDuongSoHoc,--DV
   Convert(int,NULL) as NgachDieuDuongChinh,--DW
   Convert(int,NULL) as NgachDieuDuong,--DX
   Convert(int,NULL) as NgachDieuDuongCaoDang,--DY
   Convert(int,NULL) as NgachDieuDuongTrungCap,--DZ
   Convert(int,NULL) as NgachDieuDuongSoCap,--EA
   
   Convert(int,NULL) as HoSinh,--EB
   Convert(int,NULL) as HoSinh_Nu,--EC
   Convert(int,NULL) as HoSinhDaiHoc,--ED
   Convert(int,NULL) as HoSinhCaoDang,--EE
   Convert(int,NULL) as HoSinhTrungHoc,--EF
   Convert(int,NULL) as HoSinhSoHoc,--EG
   Convert(int,NULL) as NgachHoSinhChinh,--EH
   Convert(int,NULL) as NgachHoSinh,--EI
   Convert(int,NULL) as NgachHoSinhCaoDang,--EJ
   Convert(int,NULL) as NgachHoSinhTrungCap,--EK
   Convert(int,NULL) as NgachHoSinhSoCap,--EL
   
   Convert(int,NULL) as LuongY,--EM
   Convert(int,NULL) as LuongY_Nu,--EN
   Convert(int,NULL) as DuocTa,--EO
   Convert(int,NULL) as DuocTa_Nu,--EP
   Convert(int,NULL) as NgachDuocTa,--EQ
   
   Convert(int,NULL) as TDCMSauDaiHoc,--ES
   Convert(int,NULL) as TDCMSauDaiHoc_Nu,--ET
   Convert(int,NULL) as TDCMDaiHoc,--EU
   Convert(int,NULL) as TDCMDaiHoc_Nu,--EV
   Convert(int,NULL) as TDCMCaoDang,--EW
   Convert(int,NULL) as TDCMCaoDang_Nu,--EX
   Convert(int,NULL) as TDCMTrungHoc,--EY
   Convert(int,NULL) as TDCMTrungHoc_Nu,--EZ
   
   Convert(int,NULL) as NgachCBKhacChuyenVienCaoCap,--FC
   Convert(int,NULL) as NgachCBKhacChuyenVienChinh,--FD
   Convert(int,NULL) as NgachCBKhacChuyenVien,--FE
   Convert(int,NULL) as NgachCBKhacCanSu,--FF
   Convert(int,NULL) as NgachCBKhacNhanVien--FG
   
 INTO #TempNSToChuc
 FROM dbo.CHToChucHanhChinh tchc WITH (NOLOCK)
      JOIN dbo.CHCommonCode cc WITH (NOLOCK)
      ON tchc.LoaiToChucHanhChinh = cc.CodeID
 WHERE 1=2

 CREATE INDEX #ToChucHanhChinhID ON #TempNSToChuc(ToChucHanhChinhID)

 declare @sqlString as nvarchar(max)
 declare @sqlStringWHR as nvarchar(max)
 declare @sqlParameters as nvarchar(max)
 declare @count as int
 
INSERT INTO #TempNSToChuc
SELECT 
  --tc.ToChucID as ToChucID,
  --tc.TenToChucShort,
  --tc.TenToChucFull,
  --tc.ToChucIDCha,
  --tc.ToChucLevel,
  --tc.XepHangDonViID,
   tchc.ToChucHanhChinhID as int,
   CONVERT(int, NULL) as ToChucHanhChinhID,
   tchc.TenToChucHanhChinh,
   tchc.ToChucHanhChinhIDCha,
   tchc.ToChucHanhChinhLevel,
   tchc.LoaiToChucHanhChinh,
   cc.CodeValue as TenLoaiToChucHanhChinh,
   
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  
  0,0,0,0,0,0,0,0,0,0
  --0,0,0,0,0,0,0,0,0,0

FROM dbo.CHToChucHanhChinh tchc WITH (NOLOCK)
      JOIN dbo.CHCommonCode cc WITH (NOLOCK)
      ON tchc.LoaiToChucHanhChinh = cc.CodeID
WHERE tchc.ToChucHanhChinhID IN ( SELECT DISTINCT tchcID FROM @tchcTree)
if @Debug = 1 print 'insert success'
--Tao table tam chua danh sach NhanVien cua ToChuc, sau do chi truy van vao table nay
 SELECT  
      Convert(int,null) as NhanVienID
      ,nv.[HoTenKhaiSinh]
      ,nv.[HoTenKhac]
      ,nv.[NgaySinh]
      ,nv.[DiaChiNoiSinh]
      ,nv.[DiachiIDQueQuan]
      ,nv.[GioiTinhID]
      ,nv.[DanTocID]
      ,nv.[TonGiaoID]
      ,nv.[SoCMND]
      ,nv.[NgayCap]
      ,nv.[NoiCap]
      ,nv.[DiaChiIDHoKhau]
      ,nv.[DiaChiIDNoiOHienTai]     
      ,nv.[ToChucIDTuyenDung]
      ,nv.[NgayTuyenDung]      
      ,nv.[TrinhDoGiaoDucPhoThongID]
      ,nv.[HeGiaoDucPhoThongID]
      ,nv.[TrinhDoChuyenMonCaoNhatID]
      ,nv.[LyLuanChinhTriID]
      ,nv.[QuanLyNhaNuocID]
      ,nv.[TrinhDoTinHocID]
      ,nv.[ThongTinTrinhDoChuyenMon]
      ,nv.[SoTruongCongTac]
      ,nv.[TinhTrangSucKhoe]
      ,nv.[ChieuCao]
      ,nv.[CanNang]
      ,nv.[NhomMauID]
      ,nv.[NgayVaoDangCSVN]
      ,nv.[NgayChinhThucVaoDangCSVN]
      ,nv.[ToChucChinhTriThamGia]
      ,nv.[NgayNhapNgu]
      ,nv.[NgayXuatNgu]
      ,nv.[QuanHamCaoNhatID]
      ,nv.[DanhHieuCaoNhatID]
      ,nv.[HangThuongBinhID]
      ,nv.[ConGiaDinhChinhSachID]
      ,nv.[KiLuat]
      ,nv.[QuaTrinhDongBHXH]
      ,nv.[QuaTrinhDongBHYT]
      ,nv.[SoNgayNghiPhepConLai]
      ,nv.[LichSuChinhTri]
      ,nv.[QuanHeToChucNuocNgoai]
      ,nv.[QuanHeNhanThanNuocNgoai]
      ,nv.[CoQuanQuanLiId]
      ,nv.[SoHieu]
      ,nv.[CoQuanSuDungId]
      ,nv.[LoaiBangCapID]
      ,nn.[MaNghe]
      ,nbl.[MaNgach]
      ,nng.[TenNgoaiNgu]
      ,nng.[TrinhDoNgoaiNguId]
INTO #TempNSNhanVien
FROM  dbo.NSNhanVien nv WITH (NOLOCK) 
	  JOIN dbo.NSToChuc tc WITH (NOLOCK)
	  ON nv.CoQuanSuDungId = tc.ToChucID
	  JOIN dbo.CHToChucHanhChinh tchc WITH (NOLOCK)
	  ON tc.DonViHanhChinhTrucThuocID = tchc.ToChucHanhChinhID
	  LEFT JOIN dbo.NSNgheNghiep nn WITH (NOLOCK) 
	  ON (nv.[NhanVienID] = nn.[NhanVienId])
	  LEFT JOIN dbo.NSNgachBacLuong nbl WITH (NOLOCK)
	  ON (nv.[NhanVienID] = nbl.[NhanVienId])
	  LEFT JOIN dbo.NSNgoaiNgu nng WITH (NOLOCK)
	  ON (nv.[NhanVienID] = nng.[NhanVienId])	  	  
WHERE  (nbl.NgayHieuLuc = (SELECT MAX(NgayHieuLuc) FROM NSNgachBacLuong WHERE NSNgachBacLuong.NhanVienID = nv.NhanVienID)
		OR nbl.NgayHieuLuc IS NULL)
  AND  (nn.ThoiGianThayDoi = (SELECT MAX(ThoiGianThayDoi) FROM NSNgheNghiep WHERE NSNgheNghiep.NhanVienID = nv.NhanVienID)
		OR nn.ThoiGianThayDoi IS NULL)
  AND  1=2

CREATE INDEX #TempNhanVienID ON #TempNSNhanVien(NhanVienID)

INSERT INTO #TempNSNhanVien
SELECT 
      nv.NhanVienID as NhanVienID
      ,nv.[HoTenKhaiSinh]
      ,nv.[HoTenKhac]
      ,nv.[NgaySinh]
      ,nv.[DiaChiNoiSinh]
      ,nv.[DiachiIDQueQuan]
      ,nv.[GioiTinhID]
      ,nv.[DanTocID]
      ,nv.[TonGiaoID]
      ,nv.[SoCMND]
      ,nv.[NgayCap]
      ,nv.[NoiCap]
      ,nv.[DiaChiIDHoKhau]
      ,nv.[DiaChiIDNoiOHienTai]      
      ,nv.[ToChucIDTuyenDung]
      ,nv.[NgayTuyenDung]     
      ,nv.[TrinhDoGiaoDucPhoThongID]
      ,nv.[HeGiaoDucPhoThongID]
      ,nv.[TrinhDoChuyenMonCaoNhatID]
      ,nv.[LyLuanChinhTriID]
      ,nv.[QuanLyNhaNuocID]
      ,nv.[TrinhDoTinHocID]
      ,nv.[ThongTinTrinhDoChuyenMon]
      ,nv.[SoTruongCongTac]
      ,nv.[TinhTrangSucKhoe]
      ,nv.[ChieuCao]
      ,nv.[CanNang]
      ,nv.[NhomMauID]
      ,nv.[NgayVaoDangCSVN]
      ,nv.[NgayChinhThucVaoDangCSVN]
      ,nv.[ToChucChinhTriThamGia]
      ,nv.[NgayNhapNgu]
      ,nv.[NgayXuatNgu]
      ,nv.[QuanHamCaoNhatID]
      ,nv.[DanhHieuCaoNhatID]
      ,nv.[HangThuongBinhID]
      ,nv.[ConGiaDinhChinhSachID]
      ,nv.[KiLuat]
      ,nv.[QuaTrinhDongBHXH]
      ,nv.[QuaTrinhDongBHYT]
      ,nv.[SoNgayNghiPhepConLai]
      ,nv.[LichSuChinhTri]
      ,nv.[QuanHeToChucNuocNgoai]
      ,nv.[QuanHeNhanThanNuocNgoai]
      ,nv.[CoQuanQuanLiId]
      ,nv.[SoHieu]
      ,nv.[CoQuanSuDungId]
      ,nv.[LoaiBangCapID]
      ,nn.[MaNghe]
      ,nbl.[MaNgach]
      ,nng.[TenNgoaiNgu]
      ,nng.[TrinhDoNgoaiNguId] 
FROM  dbo.NSNhanVien nv WITH (NOLOCK) 
      JOIN dbo.NSToChuc tc WITH (NOLOCK)
	  ON nv.CoQuanSuDungId = tc.ToChucID
	  JOIN dbo.CHToChucHanhChinh tchc WITH (NOLOCK)
	  ON tc.DonViHanhChinhTrucThuocID = tchc.ToChucHanhChinhID
	  LEFT JOIN dbo.NSNgheNghiep nn WITH (NOLOCK) 
	  ON (nv.[NhanVienID] = nn.[NhanVienId])
	  LEFT JOIN dbo.NSNgachBacLuong nbl WITH (NOLOCK)
	  ON (nv.[NhanVienID] = nbl.[NhanVienId])
	  LEFT JOIN dbo.NSNgoaiNgu nng WITH (NOLOCK)
	  ON (nv.[NhanVienID] = nng.[NhanVienId])	  	  
WHERE  (nbl.NgayHieuLuc = (SELECT MAX(NgayHieuLuc) FROM NSNgachBacLuong WHERE NSNgachBacLuong.NhanVienID = nv.NhanVienID)
		OR nbl.NgayHieuLuc IS NULL)
  AND  (nn.ThoiGianThayDoi = (SELECT MAX(ThoiGianThayDoi) FROM NSNgheNghiep WHERE NSNgheNghiep.NhanVienID = nv.NhanVienID)
		OR nn.ThoiGianThayDoi IS NULL)
  AND  tchc.ToChucHanhChinhID IN  (SELECT DISTINCT tchcID FROM @tchcTree)
----------
declare @tchcIDforCursor int, @tchcLevelforCursor int

declare tchcCursor cursor for 
SELECT DISTINCT tchct.tchcID, tchct.tchcLevel FROM @tchcTree tchct ORDER BY tchct.tchcLevel
OPEN tchcCursor 
FETCH NEXT FROM tchcCursor INTO @tchcIDforCursor, @tchcLevelforCursor

while @@FETCH_STATUS = 0
begin
	PRINT @tchcIDforCursor
	FETCH NEXT FROM tchcCursor INTO @tchcIDforCursor, @tchcLevelforCursor

	--Update So Luong Nhan Vien Nu	
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE  GioiTinhID = 1540 )
	Update #TempNSToChuc
	SET Nu = @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--Update So Luong Dang Vien
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE NgayVaoDangCSVN IS NOT NULL )
	Update #TempNSToChuc
	SET DangVien = @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--Update DanToc It Nguoi
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanTocID <> 1512 )
	Update #TempNSToChuc
	SET DanTocItNguoi = @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--Update Tuoi < 30
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) < 30))
	Update #TempNSToChuc
	SET Duoi30Tuoi = @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--Update Tuoi 30 -50
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) > 30) AND (datepart(year,getdate())-datepart(year,NgaySinh) < 51))
	Update #TempNSToChuc
	SET Tuoi30_50 = @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--Update  Tuoi 51 - 60
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) > 50) AND (datepart(year,getdate())-datepart(year,NgaySinh) < 61))
	Update #TempNSToChuc
	SET Tuoi51_60 = @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--Update Nu 50
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) = 54))
	Update #TempNSToChuc
	SET Nu_54Tuoi = @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--Update Name 59
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) = 59))
	Update #TempNSToChuc
	SET Nam_59Tuoi = @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--LLCT_CaoCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LyLuanChinhTriID = 1500)
	Update #TempNSToChuc
	SET  LLCT_CaoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--LLCT_TrungCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LyLuanChinhTriID = 1499)
	Update #TempNSToChuc
	SET  LLCT_TrungCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--LLCT_SoCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LyLuanChinhTriID = 1498)
	Update #TempNSToChuc
	SET  LLCT_SoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--AnhVan_CuNhan
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(TenNgoaiNgu)) = 'Anh' AND TrinhDoNgoaiNguId = 1578)
	Update #TempNSToChuc
	SET  AnhVan_CuNhan= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--AnhVan_CoSo
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(TenNgoaiNgu)) = 'Anh' AND TrinhDoNgoaiNguId = 1579)
	Update #TempNSToChuc
	SET  AnhVan_CoSo= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NNKhac_CuNhan
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(TenNgoaiNgu)) <> 'Anh' AND TrinhDoNgoaiNguId = 1578)
	Update #TempNSToChuc
	SET  NNKhac_CuNhan= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NNKhac_CoSo
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(TenNgoaiNgu)) <> 'Anh' AND TrinhDoNgoaiNguId = 1579)
	Update #TempNSToChuc
	SET  NNKhac_CoSo= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TinHoc_CuNhan
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE TrinhDoTinHocID = 1570)
	Update #TempNSToChuc
	SET  TinHoc_CuNhan= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TinHoc_CoSo
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE TrinhDoTinHocID IN(1571,1572,1573,1574))
	Update #TempNSToChuc
	SET  TinHoc_CoSo= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 

	--ThayThuocNhanDan
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanhHieuCaoNhatID =1448)
	Update #TempNSToChuc
	SET  ThayThuocNhanDan= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--ThayThuocUuTu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanhHieuCaoNhatID =1451)
	Update #TempNSToChuc
	SET  ThayThuocUuTu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NhaGiaoNhanDan
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanhHieuCaoNhatID =1447)
	Update #TempNSToChuc
	SET  NhaGiaoNhanDan= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NhaGiaoUuTu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanhHieuCaoNhatID =1450)
	Update #TempNSToChuc
	SET  NhaGiaoUuTu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 

	--TongSoBacSi
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999')
	Update #TempNSToChuc
	SET  TongSoBacSi= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TongSoBacSi_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND GioiTinhID = 1540 )
	Update #TempNSToChuc
	SET  TongSoBacSi_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--GiaoSuY
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1470 )
	Update #TempNSToChuc
	SET  GiaoSuY= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--GiaoSuY_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1470 AND GioiTinhID = 1540 )
	Update #TempNSToChuc
	SET  GiaoSuY_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--PhoGiaoSuY
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1471 )
	Update #TempNSToChuc
	SET  PhoGiaoSuY= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--PhoGiaoSuY_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1471 AND GioiTinhID = 1540 )
	Update #TempNSToChuc
	SET  PhoGiaoSuY_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TienSiY
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon IN (1472,1473) )
	Update #TempNSToChuc
	SET  TienSiY= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TienSiY_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon IN (1472,1473)  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  TienSiY_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--ThacSiY
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1478 )
	Update #TempNSToChuc
	SET  ThacSiY= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--ThacSiY_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1478  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  ThacSiY_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--BacSiCK2
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1474 )
	Update #TempNSToChuc
	SET  BacSiCK2= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--BacSiCK2_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1474 AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  BacSiCK2_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--BacSiCK1
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1475 )
	Update #TempNSToChuc
	SET  BacSiCK1= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--BacSiCK1_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1475 AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  BacSiCK1_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--BacSiNha
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9997')
	Update #TempNSToChuc
	SET  BacSiNha= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--BacSiNha_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9997' AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  BacSiNha_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachBacSi_ChuyenVienCaoCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.001' AND LTRIM(RTRIM(MaNghe)) = '9999')
	Update #TempNSToChuc
	SET  NgachBacSi_ChuyenVienCaoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachBacSi_ChuyenVienChinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.002' AND LTRIM(RTRIM(MaNghe)) = '9999')
	Update #TempNSToChuc
	SET  NgachBacSi_ChuyenVienChinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachBacSi_ChuyenVien
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.003' AND LTRIM(RTRIM(MaNghe)) = '9999')
	Update #TempNSToChuc
	SET  NgachBacSi_ChuyenVien= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachBacSiCaoCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.116')
	Update #TempNSToChuc
	SET  NgachBacSiCaoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachBacSiChinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.116')
	Update #TempNSToChuc
	SET  NgachBacSiChinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachBacSi
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.117')
	Update #TempNSToChuc
	SET  NgachBacSi= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	   
	--DuocSiDaiHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND LoaiBangCapID IN(1304,1305) )
	Update #TempNSToChuc
	SET  DuocSiDaiHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DuocSiDaiHoc_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND LoaiBangCapID IN(1304,1305) AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  DuocSiDaiHoc_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--GiaoSuDuoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1470 )
	Update #TempNSToChuc
	SET  GiaoSuDuoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--GiaoSuDuoc_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1470 AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  GiaoSuDuoc_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--PhoGiaoSuDuoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1471 )
	Update #TempNSToChuc
	SET  PhoGiaoSuDuoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--PhoGiaoSuDuoc_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1471  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  PhoGiaoSuDuoc_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TienSiDuoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon IN (1472,1473) )
	Update #TempNSToChuc
	SET  TienSiDuoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TienSiDuoc_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon IN (1472,1473)  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  TienSiDuoc_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--ThacSiDuoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1478 )
	Update #TempNSToChuc
	SET  ThacSiDuoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--ThacSiDuoc_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1478   AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  ThacSiDuoc_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DuocSiCK2
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1476 )
	Update #TempNSToChuc
	SET  DuocSiCK2= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DuocSiCK2_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1476  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  DuocSiCK2_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DuocSiCK1
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1477 )
	Update #TempNSToChuc
	SET  DuocSiCK1= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DuocSiCK1_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1477  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  DuocSiCK1_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDuocSi_ChuyenVienCaoCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.001' AND LTRIM(RTRIM(MaNghe)) = '9998')
	Update #TempNSToChuc
	SET  NgachDuocSi_ChuyenVienCaoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDuocSi_ChuyenVienChinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.002' AND LTRIM(RTRIM(MaNghe)) = '9998')
	Update #TempNSToChuc
	SET  NgachDuocSi_ChuyenVienChinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDuocSi_ChuyenVien
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.003' AND LTRIM(RTRIM(MaNghe)) = '9998')
	Update #TempNSToChuc
	SET  NgachDuocSi_ChuyenVien= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDuocSiCaoCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.132')
	Update #TempNSToChuc
	SET  NgachDuocSiCaoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDuocSiChinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.133')
	Update #TempNSToChuc
	SET  NgachDuocSiChinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDuocSi
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.134')
	Update #TempNSToChuc
	SET  NgachDuocSi= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	   
	--ThacSiYTeCongCong
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9996' AND ThongTinTrinhDoChuyenMon = 1478)
	Update #TempNSToChuc
	SET  ThacSiYTeCongCong= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--ThacSiYTeCongCong_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9996' AND ThongTinTrinhDoChuyenMon = 1478   AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  ThacSiYTeCongCong_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DaiHocYTeCongCong
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9996' AND LoaiBangCapID = 1304)
	Update #TempNSToChuc
	SET  DaiHocYTeCongCong= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DaiHocYTeCongCong_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9996' AND LoaiBangCapID = 1304  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  DaiHocYTeCongCong= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachYTeCongCongChinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16a.197')
	Update #TempNSToChuc
	SET  NgachYTeCongCongChinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachYTeCongCong
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16a.198')
	Update #TempNSToChuc
	SET  NgachYTeCongCong= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	  
	--YSi
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9995')
	Update #TempNSToChuc
	SET  YSi= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--YSi_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9995'  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  YSi_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachYSi
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.119')
	Update #TempNSToChuc
	SET  NgachYSi= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachYSiCanSu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9995' AND LTRIM(RTRIM(MaNgach)) = '1.004')
	Update #TempNSToChuc
	SET  NgachYSiCanSu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	   
	--KTVNhaKhoa
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9989')
	Update #TempNSToChuc
	SET  KTVNhaKhoa= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--KTVNhaKhoa_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9989' AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  KTVNhaKhoa_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachKTVNhaKhoa
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.999')
	Update #TempNSToChuc
	SET  NgachKTVNhaKhoa= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	   
	--KTVY
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994')
	Update #TempNSToChuc
	SET  KTVY= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--KTVY_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  KTVY_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--KTVYDaiHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND LoaiBangCapID = 1304)
	Update #TempNSToChuc
	SET  KTVYDaiHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--KTVYCaoDang
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND LoaiBangCapID = 1303)
	Update #TempNSToChuc
	SET  KTVYCaoDang= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--KTVYTrungHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND LoaiBangCapID = 1302)
	Update #TempNSToChuc
	SET  KTVYTrungHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--KTVYSoHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND LoaiBangCapID = 1301)
	Update #TempNSToChuc
	SET  KTVYSoHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachKTVYChinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.284')
	Update #TempNSToChuc
	SET  NgachKTVYChinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachKTVY
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.285')
	Update #TempNSToChuc
	SET  NgachKTVY= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachKTVYCaoDang
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.286')
	Update #TempNSToChuc
	SET  NgachKTVYCaoDang= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachKTVYTrungHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.287')
	Update #TempNSToChuc
	SET  NgachKTVYTrungHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachKTVYSoHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.288')
	Update #TempNSToChuc
	SET  NgachKTVYSoHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 

	--DuocSiTH_KTVTrungHocDuoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (LTRIM(RTRIM(MaNgach)) = '16.135' OR LTRIM(RTRIM(MaNgach)) = '16.137'))
	Update #TempNSToChuc
	SET  DuocSiTH_KTVTrungHocDuoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DuocSiTH_KTVTrungHocDuoc_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (LTRIM(RTRIM(MaNgach)) = '16.135' OR LTRIM(RTRIM(MaNgach)) = '16.137') AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  DuocSiTH_KTVTrungHocDuoc_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDuocSiTH
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (LTRIM(RTRIM(MaNgach)) = '16.135' OR LTRIM(RTRIM(MaNgach)) = '16.137'))
	Update #TempNSToChuc
	SET  NgachDuocSiTH= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	   
	--DieuDuong
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' )
	Update #TempNSToChuc
	SET  DieuDuong= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DieuDuong_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993'  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  DieuDuong_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DieuDuongDaiHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' AND LoaiBangCapID = 1304)
	Update #TempNSToChuc
	SET  DieuDuongDaiHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DieuDuongCaoDang
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' AND LoaiBangCapID = 1303)
	Update #TempNSToChuc
	SET  DieuDuongCaoDang= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DieuDuongTrungHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' AND LoaiBangCapID = 1302)
	Update #TempNSToChuc
	SET  DieuDuongTrungHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DieuDuongSoHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' AND LoaiBangCapID = 1301)
	Update #TempNSToChuc
	SET  DieuDuongSoHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDieuDuongChinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16a.199')
	Update #TempNSToChuc
	SET  NgachDieuDuongChinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDieuDuong
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16b.120')
	Update #TempNSToChuc
	SET  NgachDieuDuong= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDieuDuongCaoDang
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16a.120')
	Update #TempNSToChuc
	SET  NgachDieuDuongCaoDang= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDieuDuongTrungCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.b121')
	Update #TempNSToChuc
	SET  NgachDieuDuongTrungCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDieuDuongSoCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16b.122')
	Update #TempNSToChuc
	SET  NgachDieuDuongSoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	   
	--HoSinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' )
	Update #TempNSToChuc
	SET  HoSinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--HoSinh_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992'  AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  HoSinh_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--HoSinhDaiHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' AND LoaiBangCapID = 1304)
	Update #TempNSToChuc
	SET  HoSinhDaiHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--HoSinhCaoDang
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' AND LoaiBangCapID = 1303)
	Update #TempNSToChuc
	SET  HoSinhCaoDang= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--HoSinhTrungHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' AND LoaiBangCapID = 1302)
	Update #TempNSToChuc
	SET  HoSinhTrungHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--HoSinhSoHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' AND LoaiBangCapID = 1301)
	Update #TempNSToChuc
	SET  HoSinhSoHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachHoSinhChinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.294')
	Update #TempNSToChuc
	SET  NgachDieuDuongSoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachHoSinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.295')
	Update #TempNSToChuc
	SET  NgachHoSinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachHoSinhCaoDang
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.296')
	Update #TempNSToChuc
	SET  NgachHoSinhCaoDang= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachHoSinhTrungCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.297')
	Update #TempNSToChuc
	SET  NgachHoSinhTrungCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachHoSinhSoCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.298')
	Update #TempNSToChuc
	SET  NgachHoSinhSoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	   
	--LuongY
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9991' )
	Update #TempNSToChuc
	SET  LuongY= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--LuongY_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9991' AND GioiTinhID = 1540 )
	Update #TempNSToChuc
	SET  LuongY_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DuocTa
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9990' )
	Update #TempNSToChuc
	SET  DuocTa= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--DuocTa_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9990' AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  DuocTa_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachDuocTa
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.136')
	Update #TempNSToChuc
	SET  NgachDuocTa= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	   
	--TDCMSauDaiHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1305)
	Update #TempNSToChuc
	SET  TDCMSauDaiHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TDCMSauDaiHoc_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1305 AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  TDCMSauDaiHoc_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TDCMDaiHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1304)
	Update #TempNSToChuc
	SET  TDCMDaiHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TDCMDaiHoc_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1304 AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  TDCMDaiHoc_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TDCMCaoDang
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1303)
	Update #TempNSToChuc
	SET  TDCMCaoDang= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TDCMCaoDang_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1303 AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  TDCMCaoDang_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TDCMTrungHoc
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1302)
	Update #TempNSToChuc
	SET  TDCMTrungHoc= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--TDCMTrungHoc_Nu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1302 AND GioiTinhID = 1540)
	Update #TempNSToChuc
	SET  TDCMTrungHoc_Nu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	   
	--NgachCBKhacChuyenVienCaoCap
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '01.001')
	Update #TempNSToChuc
	SET  NgachCBKhacChuyenVienCaoCap= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachCBKhacChuyenVienChinh
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '01.002')
	Update #TempNSToChuc
	SET  NgachCBKhacChuyenVienChinh= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachCBKhacChuyenVien
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '01.003')
	Update #TempNSToChuc
	SET  NgachCBKhacChuyenVien= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachCBKhacCanSu
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '01.004')
	Update #TempNSToChuc
	SET  NgachCBKhacCanSu= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 
	--NgachCBKhacNhanVien--FG
	SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE MaNgach IS NULL)
	Update #TempNSToChuc
	SET  NgachCBKhacNhanVien= @count WHERE ToChucHanhChinhID = @tchcIDforCursor 

end

CLOSE tchcCursor
DEALLOCATE tchcCursor

SELECT * FROM #TempNSToChuc
END

GO

--exec [sp_GetNsToChucByTCHC] @ToChucHanhChinhID=12,@Debug =1;

